#!/bin/bash

# 定义变量方便修改
table_db=cy_database 
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else 
	do_date=`date -d "-1 day" +%F`  
fi 

sql="

set hive.exec.dynamic.partition.mode=nonstrict;

    insert overwrite table cy_database.dwd_college_graduate partition(dt)
    select
        id,
        name ,
        sex ,
        birthday ,
        nation ,
        idcard,
        census_register_area ,
        census_type,
        census_register_date ,
        area_code ,
        phone,
        education,
        school,
        edu_time ,
        graduation ,
        home_address ,
        enterprise_recruitment ,
        startup_code,
        personal_state ,
        work_whereabouts ,
        company_work ,
        autonomy_work ,
        special_position_work,
        unemploy_type,
        now_work_company ,
        work_other ,
        keep_study ,
        register_time,
        contact_qq ,
        contact_wei ,
        semester_census_address ,
        qcellcore ,
        hard_situation ,
        other_hard_situation ,
        archives_code ,
        remark,
        update_time,
        census_register ,
        colleges_type,
        archives_unit,
        is_internship_activity,
        work_collective_type ,
        is_local,
        is_chuangye,
        card_photo_obverse,
        card_photo_back,
        education_photo,
        report_photo,
        train_type,
        graduater_status,
        now_industry_code,
        working_area,
        dt
    from cy_database.ods_college_graduate
    where dt='2020-06-17' and id is not null;

insert overwrite table cy_database.dwd_unemploy_person partition(dt)
select
    id,
    name ,
    sex,
    birthday ,
    idcard,
    education ,
    school,
    graduation ,
    edu_time,
    qcellcore ,
    now_address,
    tel_phone as phone,
    unemploy_register_time ,
    unemploy_cause ,
    unemploy_time ,
    old_company,
    startup_code ,
    sign,
    updated_time as update_time,
    card_photo_back,
    card_photo_obverse ,
    hard_special_certificate_1,
    hard_special_certificate_2,
    hard_special_certificate_3 ,
    examine_status ,
    hard_situation ,
    is_allowance_family ,
    census_register,
    dt
from cy_database.ods_unemploy_person
where dt = '2020-06-17' and id is not null;


insert overwrite table cy_database.dwd_probation_people_info partition(dt)
select pp.id,
    pp.name,
    pp.nation,
    pp.id_card,
    pp.is_jthard_graduate,
    pp.political_status,
    pp.education,
    pp.probation_person_type,
    pp.startup_id,
    pp.birthday,
    pp.school,
    pp.edu_time,
    pp.graduation,
    pp.phone,
    pp.home_phone,
    pp.home_address,
    ee.ent_name,
    ee.social_security_number,
    ee.industry_code,
    ee.industry_type,
    ee.business_code,
    ee.business_type,
    ee.business_nature,
    ee.business_nature_code,
    ee.uniform_social_credit_code,
    pp.probation_start_string,
    pp.probation_end_string,
    pp.actual_probation_endstring,
    pp.last_handle_people,
    pp.is_retain,
    pp.updated_time
from cy_database.ods_probation_people pp
join cy_database.ods_enterprise ee on ee.ent_name = pp.ni_probation_company
where ee.dt='2020-06-17' and pp.dt='2020-06-17' and pp.id is not null;


insert overwrite table cy_database.dwd_enterprise partition(dt)
select
    id,
    user_id ,
    ent_name,
    short_name,
    social_security_number,
    industry_code ,
    industry_type,
    business_code ,
    business_type ,
    business_nature ,
    business_nature_code ,
    uniform_social_credit_code ,
    scale,
    scale_code ,
    financing_stage ,
    registered_capital ,
    registration_authority ,
    establishment ,
    introduction,
    logo ,
    corporate_welfare ,
    business_license ,
    organization_code_certificate ,
    id_card_front ,
    id_card_back,
    province ,
    province_code,
    city,
    city_code,
    area,
    area_code,
    street,
    street_code,
    address,
    latitude,
    longitude ,
    status,
    views ,
    contact_person,
    tel as phone,
    manager_name,
    manager_tel as manager_phone ,
    manager_id_card_num ,
    manager_qq ,
    manager_email ,
    manager_position ,
    created_time ,
    updated_time,
    examine_status,
    register_address ,
    sys_area_code,
    batchNumber,
    register_province ,
    register_province_code ,
    register_city ,
    register_city_code,
    register_area,
    register_area_code,
    industry_value_hundred_million,
    scale_int,
    ent_level,
    is_important,
    dt
from cy_database.ods_enterprise
where dt = '2020-06-17' and id is not null;


insert overwrite table cy_database.dwd_enterprise_person_info partition(dt)
select
    id,
    identification ,
    name ,
    province_code,
    province ,
    city_code,
    city ,
    area_code ,
    area ,
    native_code ,
    native_place ,
    home_add ,
    politics_code ,
    politics ,
    sex ,
    bir_date as birthday ,
    marital_status ,
    education_background as education ,
    graduate_school as school ,
    enterprise_code as ent_id ,
    enterprise_name as ent_name ,
    industry_code ,
    industry_type ,
    business_code ,
    business_type ,
    position_code ,
    position ,
    phone ,
    entry_work,
    out_work ,
    work_year ,
    dt
from cy_database.ods_enterprise_person_info
where dt='2020-06-17' and id is not null;



insert overwrite table cy_database.dwd_enterprise_position_require_detail partition(dt)
select
    pn.id,
    pn.ent_id,
    pn.ent_name,
    ee.industry_code,
    ee.industry_type,
    ee.business_code,
    ee.business_type,
    ee.business_nature,
    ee.business_nature_code,
    pn.name as posts_name,
    pn.function_type,
    pn.posts,
    pn.sex,
    pn.experience,
    pn.education,
    pn.salary,
    pn.age_range,
    pn.job_nature,
    pn.description,
    pn.keyword,
    pn.keyword_two,
    pn.keyword_three,
    pn.province,
    pn.province_code,
    pn.city,
    pn.city_code,
    pn.area,
    pn.area_code,
    pn.street,
    pn.street_code,
    pn.address,
    pn.latitude,
    pn.longitude,
    pn.status,
    pn.views,
    pn.publish,
    pn.skill,
    pn.sys_area_code,
    pn.batchNumber,
    pn.age_begin,
    pn.age_end,
    pn.salary_begin,
    pn.salary_end,
    pn.source,
    pn.updated_time as update_time,
    pn.dt
from cy_database.ods_position pn
join cy_database.ods_enterprise ee
on pn.ent_name=ee.ent_name
where pn.dt = '2020-06-17' and pn.id is not null and ee.dt='2020-06-17' and ee.id is not null;



insert overwrite table cy_database.dwd_enterprise_business partition(dt)
select
    id,
    use_id,
    name_code as ent_id,
    ent_name,
    turnover,
    turnover_profit,
    market_value,
    risk_info,
    warning_info,
    registered_capital,
    registered_capital_update,
    alter_time,
    dt
from cy_database.ods_enterprise_business
where dt = '2020-06-17' and id is not null;


insert overwrite table cy_database.dwd_job_fair partition(dt)
select
id,
hold_date,
hold_address,
week_day,
job_fair_type,
key_service_type,
enterprise_sum,
position_sum,
admission_sum,
reach_work_sum,
sign_ldht_sum,
work_person,
job_fair_name,
dt
from cy_database.ods_job_fair
where dt = '2020-06-17' and id is not null;



insert overwrite table cy_database.dwd_enterprise_probation_years partition(dt)
select
    id,
    enterpise_id as ent_id,
    probation_company as ent_name,
    business_nature_code,
    business_nature,
    plan_probation_total,
    actual_probation_total,
    probation_retain,
    already_provide_alimony,
    already_probation_subsidy,
    probation,
    credit_code,
    updated_time,
    dt
from cy_database.ods_company_probation_years
where dt = '2020-06-17' and id is not null;



insert overwrite table cy_database.dwd_out_work partition(dt)
select
    id,
    name,
    tel_phone as phone,
    birthday,
    idcard,
    sex,
    census_province_code ,
    census_province,
    census_city_code,
    census_city,
    census_area_code,
    census_area,
    census_town_code,
    census_town ,
    census_village_code,
    census_village,
    skill_specialty,
    skill_level,
    is_work,
    work_status,
    work_town_code,
    work_town,
    work_company,
    work_position,
    work_salary,
    out_work_address ,
    out_work_company,
    out_work_position ,
    out_date ,
    is_back ,
    out_salary ,
    ni_back_company ,
    ni_positon,
    expect_salary,
    remark ,
    is_settle ,
    education,
    school ,
    entrance_time,
    edu_time ,
    graduation ,
    title_name ,
    title_time ,
    updated_time as update_time ,
    dt
from cy_database.ods_out_work
where dt = '2020-06-17' and id is not null;


insert overwrite table cy_database.dwd_person_apply_job partition(dt)
select
    id,
    name ,
    id_card as idcard,
    sex,
    mobile as phone,
    personal_state ,
    job_nature ,
    expect_job_type,
    expect_industry ,
    expect_job_province ,
    expect_job_city ,
    expect_job_area ,
    expect_job_province_code ,
    expect_job_city_code,
    expect_job_area_code ,
    expect_job_salary ,
    education,
    group_type ,
    work_experience,
    certificate ,
    create_time ,
    update_time,
    mail,
    work_year ,
    now_province ,
    now_city,
    now_area ,
    now_province_code ,
    now_city_code ,
    now_area_code ,
    now_address,
    hope_salary_small ,
    hope_salary_big,
    birthday,
    registered_province_code ,
    registered_province ,
    registered_city_code ,
    registered_city ,
    registered_area_code ,
    registered_area ,
    now_country ,
    marriage_code,
    politicalStatus ,
    health_situation_code,
    talents_type,
    work_year_code ,
    now_positionOne ,
    now_positionTwo,
    now_positionThree,
    now_industry_code ,
    previous_positionOne ,
    previous_positionTwo ,
    previous_positionThree,
    previous_industry ,
    now_salary_code ,
    now_work_situation ,
    unemployment_situation,
    talents_input,
    personCredit_level ,
    is_loan,
    is_insurance,
    dt
from cy_database.ods_person_info
where dt = '2020-06-17' and id is not null;


"

$hive -e "$sql"